﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Text;
using System.Transactions;

namespace APICommond
{
    public class ImportExcel
    {
        /// <summary>
        /// 读取exce 文件中的表页名称
        /// </summary>
        /// <param name="path">excel文件路径</param>
        /// <returns>表名列表</returns>
        public static DataSet GetExcelTableName(string path)
        {
            DataSet ds = null;
            OleDbConnection _ExcelConn = null;
            try
            {
                if (System.IO.File.Exists(path))
                {
                    ds = new DataSet();
                    string strConn = "";
                    if (path.Contains(".xlsx"))
                    {
                        strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=YES\"";
                    }
                    else
                    {
                        strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + path + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
                    }
                    _ExcelConn = new OleDbConnection(strConn);
                    _ExcelConn.Open();
                    DataTable _Table = _ExcelConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    _ExcelConn.Close();
                    ds.Tables.Add(_Table);
                    return ds;
                }
                return null;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 读取excel文件中某一表页的数据
        /// </summary>
        /// <param name="path">excel文件路径</param>
        /// <param name="tablename">表页名称</param>
        /// <returns>表页数据</returns>
        public static DataSet GetExcelData(string path, out string Error)
        {
            DataSet ds = null;

            Error = "";
            OleDbConnection _ExcelConn = null;
            try
            {
                if (System.IO.File.Exists(path))
                {
                    string strConn = "";
                    ds = new DataSet();
                    if (path.Contains(".xlsx"))
                    {
                        strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=YES\"";
                    }
                    else
                    {
                        strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + path + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
                    }
                    _ExcelConn = new OleDbConnection(strConn);
                    _ExcelConn.Open();
                    OleDbCommand cmd = _ExcelConn.CreateCommand();
                    cmd.CommandText = "select * from [资产负债表$]";
                    OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                    da.Fill(ds, "ExcelData");
                    _ExcelConn.Close();
                    return ds;
                }
                return null;
            }
            catch (Exception ex)
            {
                Error = ex.Message;
                return null;
            }

        }

        public static string CreateExcel(DataTable table)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("<meta http-equiv=\"Content-Type\" content=\"application/vnd.ms-excel;charset=utf-8\"/>");
            sb.Append("<table cellspacing=\"0\" rules=\"all\" border=\"1\" style=\"border-collapse:collapse;\">");
            sb.Append("<tr>");
            StringBuilder sb1 = new StringBuilder();

            foreach (DataColumn col in table.Columns)
            {
                if (col.ColumnName == "xh")
                {
                    continue;
                }
                else if (col.ColumnName == "zd")
                {
                    continue;
                }
                else if (col.ColumnName == "xm")
                {
                    sb.Append("<td>项目</td>");

                }
                else if (col.ColumnName == "hj")
                {
                    sb.Append("<td>合计</td>");

                }
                else
                {
                    sb.Append("<td>" + col.ColumnName + "</td>");
                }
            }
            sb.Append("</tr>");
            foreach (DataRow row in table.Rows)
            {
                sb1.Append("<tr>");
                foreach (DataColumn c in table.Columns)
                {
                    if (c.ColumnName == "xh")
                    {
                        continue;
                    }
                    else if (c.ColumnName == "zd")
                    {
                        continue;
                    }
                    else
                    {
                        sb1.Append("<td>" + row[c] + "</td>");
                    }
                }
                sb.Append("</tr>");
            }
            sb.Append(sb1.ToString());
            return sb.ToString();
        }

        public static decimal GetAmount(DataRow dr, string col, int month)
        {
            decimal d = 0;
            for (int i = 1; i <= month; i++)
            {
                decimal l = 0;
                string c = col + month;
                decimal.TryParse(dr[c].ToString(),out l);
                d = d + l ;
            }
            return d;
        }
    }
}
